﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlServerCe;
using System.Configuration;

namespace SWEN3WebService.Models
{
    public class BookingDBManager
    {
        public static int CheckIn(BookingList details)
        {
            SqlCeConnection conn = null;
            int BookingNo = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BookingList(LastName,FirstName,AdultsNo,ChildsNo,PhoneNo,EmailAddress,Address,PaymentType,Remarks,Country)" +
                    " VALUES (@LastName,@FirstName,@AdultsNo,@ChildsNo,@PhoneNo,@EmailAddress,@Address,@PaymentType,@Remarks,@Country)";
                comm.Parameters.AddWithValue("@LastName", details.LastName);
                comm.Parameters.AddWithValue("@FirstName", details.FirstName);
                comm.Parameters.AddWithValue("@AdultsNo", details.AdultsNo);
                comm.Parameters.AddWithValue("@ChildsNo", details.ChildsNo);
                comm.Parameters.AddWithValue("@PhoneNo", details.PhoneNo);
                comm.Parameters.AddWithValue("@EmailAddress", details.EmailAddress);
                comm.Parameters.AddWithValue("@Address", details.Address);
                comm.Parameters.AddWithValue("@PaymentType", details.PaymentType);
                comm.Parameters.AddWithValue("@Remarks", details.Remarks);
                comm.Parameters.AddWithValue("@Country", details.Country);
                if (comm.ExecuteNonQuery() > 0)
                {
                    SqlCeCommand comm2 = new SqlCeCommand();
                    comm2.Connection = conn;
                    comm2.CommandText = "SELECT * FROM BookingList WHERE BookingNo IN (SELECT MAX(BookingNo) FROM BookingList)";
                    comm2.ExecuteNonQuery();
                    SqlCeDataReader dr = comm2.ExecuteReader();
                    if (dr.Read())
                    {
                        BookingNo = (int)dr["BookingNo"];
                    }
                    dr.Close();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return BookingNo;
        }
    }
}